﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoyoEden.Models;
using System.Data.Common;
using System.Data;

namespace CoyoEden.Providers
{
	public partial class DbPortalProvider:PortalProvider
	{

		#region Rates

        public override Rate SelectRate(int id)
		{
			Rate item = new Rate();
			using(DbConnection conn=MyDbFactory.CreateConnection()){//using1
				conn.ConnectionString = connStringStatic;
				using(DbCommand cmd=conn.CreateCommand()){
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("SELECT Id, Title,Memo,Rating,Raters,Hits FROM {0}Rates WHERE Id={1}id;",tablePrefix,parmPrefix);
					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = id;
					cmd.Parameters.Add(dpID);

					conn.Open();
					
					using(DbDataReader rdr=cmd.ExecuteReader()){//using2
						if (rdr.HasRows) {
							rdr.Read();
							item.Title = rdr.GetString(1);
							item.Memo = rdr.GetString(2);
							item.Rating = rdr.GetFloat(3);
							item.Raters = rdr.GetString(4);
							item.Hits = rdr.GetInt32(5);
							item.Id = rdr.GetInt32(0);
						}
						
					}//endof using2
				}
			}//endof using1
			return item;
		}

        public override void InsertRate(Rate item)
		{
			using(DbConnection conn=MyDbFactory.CreateConnection()){
				conn.ConnectionString = connStringStatic;
				using(DbCommand cmd=conn.CreateCommand()){
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("INSERT INTO {0}Rates (Title,Memo,Rating,Raters,Hits) Values ({1}title,{1}memo,{1}rating,{1}raters,{1}hits);",tablePrefix,parmPrefix);
					sql.AppendFormat(" SELECT Id FROM {0}Rates WHERE Title={1}title;",tablePrefix,parmPrefix);

					cmd.CommandType = CommandType.Text;
					cmd.CommandText = sql.ToString();

					DbParameter dpTitle = MyDbFactory.CreateParameter();
					dpTitle.ParameterName = parmPrefix + "title";
					dpTitle.Value = item.Title;
					cmd.Parameters.Add(dpTitle);

					DbParameter dpMemo = MyDbFactory.CreateParameter();
					dpMemo.ParameterName = parmPrefix + "memo";
					dpMemo.Value = item.Memo;
					cmd.Parameters.Add(dpMemo);

					DbParameter dpRating = MyDbFactory.CreateParameter();
					dpRating.ParameterName = parmPrefix + "rating";
					dpRating.Value = item.Rating;
					cmd.Parameters.Add(dpRating);

					DbParameter dpRaters = MyDbFactory.CreateParameter();
					dpRaters.ParameterName = parmPrefix + "raters";
					dpRaters.Value = item.Raters;
					cmd.Parameters.Add(dpRaters);

					DbParameter dpHits = MyDbFactory.CreateParameter();
					dpHits.ParameterName = parmPrefix + "hits";
					dpHits.Value = item.Hits;
					cmd.Parameters.Add(dpHits);

					var id = cmd.ExecuteScalar();
					item.Id = Convert.ToInt32(id);

				}
			}
		}
		
		/// <summary>
		/// Batch insert
		/// </summary>
		/// <param name="items"></param>
        public override void InsertRates(List<Rate> items)
        {
			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				items.ForEach(item => {
					InsertRate(item, conn, tablePrefix, parmPrefix);
				});
			}
		}
		/// <summary>
		/// batch update
		/// </summary>
		/// <param name="items"></param>
        public override void UpdateRates(List<Rate> items)
		{
			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				items.ForEach(item =>
				{
					UpdateRate(item, conn, tablePrefix, parmPrefix);
				});
			}
		}
        public override void UpdateRate(Rate item)
		{
			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("UPDATE {0}Rates SET Title={1}title,Memo={1}memo,Rating={1}rating,Raters={1}raters,Hits={1}hits WHERE Id={1}id;", tablePrefix, parmPrefix);

					cmd.CommandType = CommandType.Text;
					cmd.CommandText = sql.ToString();

					DbParameter dpTitle = MyDbFactory.CreateParameter();
					dpTitle.ParameterName = parmPrefix + "title";
					dpTitle.Value = item.Title;
					cmd.Parameters.Add(dpTitle);

					DbParameter dpMemo = MyDbFactory.CreateParameter();
					dpMemo.ParameterName = parmPrefix + "memo";
					dpMemo.Value = item.Memo;
					cmd.Parameters.Add(dpMemo);

					DbParameter dpRating = MyDbFactory.CreateParameter();
					dpRating.ParameterName = parmPrefix + "rating";
					dpRating.Value = item.Rating;
					cmd.Parameters.Add(dpRating);

					DbParameter dpRaters = MyDbFactory.CreateParameter();
					dpRaters.ParameterName = parmPrefix + "raters";
					dpRaters.Value = item.Raters;
					cmd.Parameters.Add(dpRaters);

					DbParameter dpHits = MyDbFactory.CreateParameter();
					dpHits.ParameterName = parmPrefix + "hits";
					dpHits.Value = item.Hits;
					cmd.Parameters.Add(dpHits);

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = item.Id;
					cmd.Parameters.Add(dpID);
					
					cmd.ExecuteNonQuery();

				}
			}
		}

        public override void DeleteRate(Rate item)
		{
			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("DELETE FROM {0}VoteItem WHERE RateID = {1}id;", tablePrefix, parmPrefix);
					sql.AppendFormat("DELETE FROM {0}Rates WHERE Id = {1}id;",tablePrefix,parmPrefix);
					
					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = item.Id;
					cmd.Parameters.Add(dpID);

					cmd.ExecuteNonQuery();
				}
			}
		}
		/// <summary>
		/// batch delete
		/// </summary>
		/// <param name="items"></param>
        public override void DeleteRates(List<Rate> items)
		{
			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				items.ForEach(item =>
				{
					DeleteRate(item, conn, tablePrefix, parmPrefix);
				});
			}
		}
        public override List<Rate> FillRates()
		{
			List<Rate> rates = new List<Rate>();
			using(DbConnection conn=MyDbFactory.CreateConnection()){
				conn.ConnectionString = connStringStatic;
				using(DbCommand cmd=conn.CreateCommand()){
					cmd.CommandType = CommandType.Text;
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("SELECT Id, Title,Memo,Rating,Raters,Hits FROM {0}Rates ;",tablePrefix);

					cmd.CommandText = sql.ToString();
					conn.Open();
					using(DbDataReader rdr=cmd.ExecuteReader()){
						while(rdr.Read()){
							rates.Add(LoadRateFromReader(rdr));
						}
					}
				}
			
			}
			return rates;
		}
		#endregion

		#region helper methods

		private Rate LoadRateFromReader(DbDataReader rdr) {
			var item = new Rate();
			item.Title = rdr.GetString(1);
			item.Memo = rdr.GetString(2);
			item.Rating = rdr.GetFloat(3);
			item.Raters = rdr.GetString(4);
			item.Hits = rdr.GetInt32(5);
			item.Id = rdr.GetInt32(0);

			item.MarkOld();
			return item;
		}
		public static  void UpdateRate(Rate item,DbConnection conn, string tablePrefix, string parmPrefix)
		{
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("UPDATE {0}Rates SET Title={1}title,Memo={1}memo,Rating={1}rating,Raters={1}raters,Hits={1}hits WHERE Id={1}id;", tablePrefix, parmPrefix);

					cmd.CommandType = CommandType.Text;
					cmd.CommandText = sql.ToString();

					DbParameter dpTitle = MyDbFactory.CreateParameter();
					dpTitle.ParameterName = parmPrefix + "title";
					dpTitle.Value = item.Title;
					cmd.Parameters.Add(dpTitle);

					DbParameter dpMemo = MyDbFactory.CreateParameter();
					dpMemo.ParameterName = parmPrefix + "memo";
					dpMemo.Value = item.Memo;
					cmd.Parameters.Add(dpMemo);

					DbParameter dpRating = MyDbFactory.CreateParameter();
					dpRating.ParameterName = parmPrefix + "rating";
					dpRating.Value = item.Rating;
					cmd.Parameters.Add(dpRating);

					DbParameter dpRaters = MyDbFactory.CreateParameter();
					dpRaters.ParameterName = parmPrefix + "raters";
					dpRaters.Value = item.Raters;
					cmd.Parameters.Add(dpRaters);

					DbParameter dpHits = MyDbFactory.CreateParameter();
					dpHits.ParameterName = parmPrefix + "hits";
					dpHits.Value = item.Hits;
					cmd.Parameters.Add(dpHits);

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = item.Id;
					cmd.Parameters.Add(dpID);

					cmd.ExecuteNonQuery();

				}
		}
		public static void DeleteRate(Rate item, DbConnection conn, string tablePrefix, string parmPrefix)
		{
			using (DbCommand cmd = conn.CreateCommand())
			{
				StringBuilder sql = new StringBuilder();
				sql.AppendFormat("DELETE FROM {0}VoteItem WHERE RateID = {1}id;", tablePrefix, parmPrefix);
				sql.AppendFormat("DELETE FROM {0}Rates WHERE Id = {1}id;", tablePrefix, parmPrefix);

				cmd.CommandText = sql.ToString();
				cmd.CommandType = CommandType.Text;

				DbParameter dpID = MyDbFactory.CreateParameter();
				dpID.ParameterName = parmPrefix + "id";
				dpID.Value = item.Id;
				cmd.Parameters.Add(dpID);

				cmd.ExecuteNonQuery();
			}
		}
		public static void InsertRate(Rate item, DbConnection conn, string tablePrefix, string parmPrefix)
		{
			using (DbCommand cmd = conn.CreateCommand())
			{
				StringBuilder sql = new StringBuilder();
				sql.AppendFormat("INSERT INTO {0}Rates (Title,Memo,Rating,Raters,Hits) Values ({1}title,{1}memo,{1}rating,{1}raters,{1}hits);", tablePrefix, parmPrefix);
				sql.AppendFormat(" SELECT Id FROM {0}Rates WHERE Title={1}title;", tablePrefix, parmPrefix);

				cmd.CommandType = CommandType.Text;
				cmd.CommandText = sql.ToString();

				DbParameter dpTitle = MyDbFactory.CreateParameter();
				dpTitle.ParameterName = parmPrefix + "title";
				dpTitle.Value = item.Title;
				cmd.Parameters.Add(dpTitle);

				DbParameter dpMemo = MyDbFactory.CreateParameter();
				dpMemo.ParameterName = parmPrefix + "memo";
				dpMemo.Value = item.Memo;
				cmd.Parameters.Add(dpMemo);

				DbParameter dpRating = MyDbFactory.CreateParameter();
				dpRating.ParameterName = parmPrefix + "rating";
				dpRating.Value = item.Rating;
				cmd.Parameters.Add(dpRating);

				DbParameter dpRaters = MyDbFactory.CreateParameter();
				dpRaters.ParameterName = parmPrefix + "raters";
				dpRaters.Value = item.Raters;
				cmd.Parameters.Add(dpRaters);

				DbParameter dpHits = MyDbFactory.CreateParameter();
				dpHits.ParameterName = parmPrefix + "hits";
				dpHits.Value = item.Hits;
				cmd.Parameters.Add(dpHits);

				var id = cmd.ExecuteScalar();
				item.Id = Convert.ToInt32(id);

			}
		}
		public static Rate SelectRate(int id, DbConnection conn, string tablePrefix, string parmPrefix)
		{
			Rate item = new Rate();
			//conn.ConnectionString = connStringStatic;
			using (DbCommand cmd = conn.CreateCommand())
			{
				StringBuilder sql = new StringBuilder();
				sql.AppendFormat("SELECT Id, Title,Memo,Rating,Raters,Hits FROM {0}Rates WHERE Id={1}id;", tablePrefix, parmPrefix);
				cmd.CommandText = sql.ToString();
				cmd.CommandType = CommandType.Text;

				DbParameter dpID = MyDbFactory.CreateParameter();
				dpID.ParameterName = parmPrefix + "id";
				dpID.Value = id;
				cmd.Parameters.Add(dpID);

				using (DbDataReader rdr = cmd.ExecuteReader())
				{//using2
					if (rdr.HasRows)
					{
						rdr.Read();
						item.Title = rdr.GetString(1);
						item.Memo = rdr.GetString(2);
						item.Rating = rdr.GetFloat(3);
						item.Raters = rdr.GetString(4);
						item.Hits = rdr.GetInt32(5);
						item.Id = rdr.GetInt32(0);
					}

				}//endof using2
			}
			return item;
		}
		#endregion
	}
}
